package com.xml2db;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

/**
 * xlsx文件阅读器实现类
 * 
 * @author levy
 *
 */
@Component
public class ExcelReader implements Reader {

	private Logger logger = LoggerFactory.getLogger(ExcelReader.class);

	@Autowired
	private DBWriter dbWriter;

	public void read(String path) {
		File folder = new File(path);
		foreachFile(folder);
		logger.info("全部操作已经完成");
	}

	/**
	 * 递归读取文件并写进数据库
	 * 
	 * @param file
	 */
	private void foreachFile(final File file) {
		if (!file.exists()) {
			System.out.println(file.getAbsolutePath() + "不存在");
		}
		if (file.isDirectory()) {
			File[] listFiles = file.listFiles();
			for (File listFile : listFiles) {
				foreachFile(listFile);
			}
		} else if (file.isFile()) {
			List<DBTable> tables = parseFile2Tables(file);
			logger.info("读取文件{}已完成", file.getName());
			logger.info("准备写入数据库");
			dbWriter.writeToDB(tables);
			logger.info("写入数据库完成");
		}
	}

	/**
	 * 将xlsx文件解析为数据表
	 * 
	 * @param file
	 */
	private List<DBTable> parseFile2Tables(File file) {
		List<DBTable> dbTables = new ArrayList<DBTable>();
		FileInputStream fis = null;
		XSSFWorkbook workbook = null;
		String fileName = file.getName();
		String name = removeFileNameSuffix(fileName);
		try {
			fis = new FileInputStream(file);
			workbook = new XSSFWorkbook(fis);
			int numberOfSheets = workbook.getNumberOfSheets();
			for (int s = 0; s < numberOfSheets; s++) {// 迭代文件中的每一张表
				XSSFSheet sheet = workbook.getSheetAt(s);
				String sheetName = sheet.getSheetName();
				DBTable table = new DBTable();// 一张表对应一个DBTable
				table.setTableName(name + "_" + sheetName);

				for (int r = 0; r <= sheet.getLastRowNum(); r++) {// 迭代表中的每一行
					XSSFRow row = sheet.getRow(r);
					switch (r) {
					case 0:// 第一行数据是数据库字段名
						parse2Fields(row, table);
						break;
					case 1:// 第二行数据是字段对应的类型
						paese2FieldsType(row, table);
						break;
					case 2:// 第三行数据是该字段的描述，不作处理
						break;
					default:// 剩余的行中的数据是字段对应的数据值
						parse2Datas(row, table);
						break;
					}
				}
				dbTables.add(table);
			}
		} catch (FileNotFoundException e) {
			throw new RuntimeException(
					"can not find the file:[" + fileName + "]");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (fis != null)
				try {
					fis.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			if (workbook != null)
				try {
					workbook.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
		}
		return dbTables;
	}

	/**
	 * 去除文件名后缀
	 * 
	 * @param path
	 * @return
	 */
	private String removeFileNameSuffix(String path) {
		int lastIndexOf = path.lastIndexOf(".");
		String name = path.substring(0, lastIndexOf);
		return name;
	}

	/**
	 * 将行数据解析到数据表对象中字段名集合
	 * 
	 * @param row
	 * @param table
	 */
	private void parse2Fields(XSSFRow row, DBTable table) {
		List<String> fields = table.getFields();
		for (int i = 0; i < row.getLastCellNum(); i++) {
			XSSFCell cell = row.getCell(i);
			fields.add(cell.getStringCellValue());
		}
	}

	/**
	 * 将行数据解析到数据表对象中字段类型集合
	 * 
	 * @param row
	 * @param table
	 */
	private void paese2FieldsType(XSSFRow row, DBTable table) {
		List<String> fieldsTypes = table.getFieldsTypes();
		for (int i = 0; i < row.getLastCellNum(); i++) {
			XSSFCell cell = row.getCell(i);
			fieldsTypes.add(cell.getStringCellValue());
		}
	}

	/**
	 * 将行数据解析到数据表对象中字段值集合
	 * 
	 * @param row
	 * @param table
	 */
	private void parse2Datas(XSSFRow row, DBTable table) {
		List<Object[]> datas = table.getDatas();
		String[] data = new String[row.getLastCellNum()];
		for (int i = 0; i < row.getLastCellNum(); i++) {
			XSSFCell cell = row.getCell(i);
			String value = null;
			switch (cell.getCellType()) {
			case Cell.CELL_TYPE_NUMERIC:
				value = String.valueOf(cell.getNumericCellValue());
				break;
			case Cell.CELL_TYPE_STRING:
				value = cell.getStringCellValue();
			default:
				break;
			}
			data[i] = value;
		}
		datas.add(data);
	}

}
